package com.gopay.scheduler.service.reporthandler.impl;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.springframework.stereotype.Service;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;

import com.gopay.common.domain.report.ReportConsoleBalanceQuery;
import com.gopay.common.order.dao.CommonQueryDAO;
import com.gopay.scheduler.service.reporthandler.AcctBalanceService;

@Service("acctBalanceService")
public class AcctBalanceServiceImpl implements AcctBalanceService {
	
	 /**
     * 公共查询Dao
     */
    @Resource(name = "commonQueryDAO")
    private CommonQueryDAO commonQueryDAO;

    
    @Override
    public ReportConsoleBalanceQuery queryAcctBalanceByAcctNo(String yesterday, String today,String acctNo){
    	List<ReportConsoleBalanceQuery> acctBalanceList = this.queryAcctBalance(yesterday, today, acctNo, 1, 1);
    	if(CollectionUtils.isEmpty(acctBalanceList)){
    		return null;
    	}
    	return acctBalanceList.get(0);
    	
    }

    @Override
	 public List<ReportConsoleBalanceQuery> queryAcctBalance(String yesterday, String today, int pageNum, int pageSize) {
		 return this.queryAcctBalance(yesterday, today, null, pageNum, pageSize);
	 }
    
	 private List<ReportConsoleBalanceQuery> queryAcctBalance(String yesterday, String today, String acctNo, int pageNum, int pageSize) {
        List<ReportConsoleBalanceQuery> acctBalList = new ArrayList<ReportConsoleBalanceQuery>();
        Map<String, Object> map = new HashMap<String, Object>();
        StringBuilder sb = new StringBuilder();

        sb.append(" select a.acct_id, ");
        sb.append("  a.prod_code, ");
        sb.append(" a.cust_id, ");
        sb.append("  c.CUST_NAME, ");
        sb.append("  c.EMAIL, ");
        sb.append("  c.MOB, ");
        sb.append("  nvl(t1.cur_bal_after_trans, a.bal_amt), ");
        sb.append("  a.frez_amt ");
        sb.append(" from acps_acct_mast a ");
        sb.append(" left join gopay_all_cust_info c on a.cust_id = c.CUST_ID ");
        sb.append("  left join (select t.* ");
        sb.append("          from (select d.acct_id, ");
        sb.append("                     d.tran_time, ");
        sb.append("                      d.cur_bal_after_trans, ");
        sb.append("                      row_number() over(partition by d.acct_id order by d.tran_time desc) row_num ");
        sb.append("                 from acps_acct_trans_dtl d ");
        sb.append("                where 1 = 1 ");
        sb.append("                 and d.tran_time < ");
        sb.append("                     to_timestamp(:today, 'yyyymmdd') ");
        sb.append("                  and d.tran_time >= ");
        sb.append("                      to_timestamp(:yesterday, 'yyyymmdd')) t ");
        sb.append("        where t.row_num = 1) t1 on a.acct_id = t1.acct_id ");
        sb.append(" where 1 = 1 ");
        
        if(!StringUtils.isEmpty(acctNo)){
        	sb.append(" and a.acct_id=:acctNo");
        	map.put("acctNo", acctNo);
        }
        
        sb.append(" order by a.created_ts asc ");

        
        map.put("today", today);
        map.put("yesterday", yesterday);

        List<Object[]> rsList = commonQueryDAO.findBySql(sb.toString(), map, pageNum, pageSize);
        ReportConsoleBalanceQuery r = null;
        if (rsList != null && rsList.size() > 0) {
            for (Object[] arr : rsList) {
                r = new ReportConsoleBalanceQuery();
                r.setAcctId((String) arr[0]);
                r.setAcctProdCode((String) arr[1]);
                r.setCustId((String) arr[2]);
                r.setCustName((String) arr[3]);
                r.setCustEmail((String) arr[4]);
                r.setCustMobile((String) arr[5]);
                r.setBalAmt((BigDecimal) arr[6]);
                r.setFrezAmt((BigDecimal) arr[7]);
                
                acctBalList.add(r);
            }
        }

        return acctBalList;
    }

}
